from openpyxl import load_workbook
import datetime

wb = load_workbook("InputData.xlsx")
print(wb.sheetnames)
ws = wb.worksheets[0]
TimeL1=[]
TimeL2=[]
TimeL3=[]
TimeL4=[]
for i in range(len(ws["B"])):
    TimeL1.append(str(ws["G%d"%(i+2)].value))
for i in range(len(ws["C"])-2):
    if(len(str(ws["C%d" % (i + 2)].value))>5):
        TimeL2.append(str(ws["C%d" % (i + 2)].value))
    else:
        # print(ws["C%d" % (i + 2)].value)
        ws["C%d" % (i + 2)]=str(ws["C%d" % (i + 2)].value)+":00"

for i in range(len(ws["G"])):
    TimeL3.append(str(ws["G%d"%(i+2)].value))
for i in range(len(ws["H"])-2):
    if(len(str(ws["H%d" % (i + 2)].value))>5):
        TimeL4.append(str(ws["H%d" % (i + 2)].value))
    else:
        # print(ws["C%d" % (i + 2)].value)
        ws["H%d" % (i + 2)]=str(ws["H%d" % (i + 2)].value)+":00"
# wb.save("InputData.xlsx")

# print(TimeL[0][8])
# print(TimeL1)
# TimeL1.pop(-1)
# TimeL1.pop(-1)
TimeA=[]
TimeG=[]
indexT1=0
for i in TimeL2:
    TimeA.append(datetime.datetime.strptime(TimeL1[indexT1][0:11]+i,'%Y-%m-%d %H:%M:%S'))
    indexT1+=1
indexT1=0
for i in TimeL4:
    TimeG.append(datetime.datetime.strptime(TimeL3[indexT1][0:11]+i,'%Y-%m-%d %H:%M:%S'))
    # print(indexT1)
    indexT1 += 1
delTime=[]
for i in range(len(TimeA)):
    delTime.append((TimeG[i]-TimeA[i]).seconds)


# print((TimeA[189]-TimeA[0]).seconds)
# print(len(TimeA))
indexDelTime=0
# print(len(ws["M"]))
for i in range(249):
    ws["O%d" %(i+2)]=delTime[indexDelTime]
    indexDelTime+=1
wb.save("InputData.xlsx")
